const Router = require('koa-router');
const router = new Router();
const DB = require('../modules/db');

router.get('/', async ctx => {
    try {
      const sql = 'select * from student';
      const result = await DB.query(sql);
      ctx.body = {
        code: 200,
        message: '成功',
        data: result
      };
    }
    catch (error) {
      ctx.throw(error);
    }
});

// 新增
router.post('/add', async ctx => {
  try {
    const {name, age, address, email, sex} = ctx.request.body;
    const sql = `INSERT INTO student(name, age, address, email, sex) VALUES ('${name}', ${age}, '${address}', '${email}', '${sex}');`;
    await DB.query(sql);
    ctx.body = {
      code: 200,
      message: '添加成功'
    };
  }
  catch (error) {
    ctx.throw(error);
  }
});

// UPDATE student SET column1 = new_value, column2 = new_value WHERE id = ${id};
// 更新学生信息
router.post('/update', async ctx => {
  try {
    const {id} = ctx.request.body;
    let str = '';
    for (const key in ctx.request.body) {
      if (key !== 'id') {
        str += `${key} = '${ctx.request.body[key]}',`
      }
    }
    const mysql = `UPDATE student SET ${str.slice(0, str.length - 1)} where id = ${id}`;
    await DB.query(mysql);
    ctx.body = {
      code: 200,
      message: '更新成功'
    };
  }
  catch (error) {
    ctx.throw(error);
  }
});

// DELETE FROM table_name WHERE condition;
router.post('/delete', async ctx => {
  try {
    const {id} = ctx.request.body;
    const mysql = `DELETE FROM student WHERE id = '${id}'`;
    await DB.query(mysql);
    ctx.body = {
      code: 200,
      message: '删除成功'
    };
  }
  catch (error) {
    ctx.throw(error);
  }
});


module.exports = router;